package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.Page;
import com.ws.shop.dao.MallDao;
import com.ws.shop.entity.Mall;
import com.ws.shop.entity.Order;
import com.ws.shop.entity.PointcLog;
import com.ws.shop.util.JdbcUtil;
import com.ws.shop.util.StringUtil;

@Repository(value="mallDao")
public class MallDaoImpl extends BaseDaoImpl implements  MallDao {
	final String findMallPageByTitle_count = "select count(1) from jvf_mall";
	final String findMallPageByTitle_list = "select id,gid,title,goodspic,costprice,price,inventory,currentnum,recommend,integral,type,state,html from jvf_mall";
	final String findMallPageByTitle_order = " order by id";
	public Page findMallPageByTitle(int pageno,int pagesize, String title) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if(!StringUtil.isEmpty(title)){
			query.append(" and title like %").append(title).append("%");
		}
		return findPage(pageno,pagesize,new StringBuilder(findMallPageByTitle_count).append(query).toString(),new StringBuilder(findMallPageByTitle_list).append(query).append(findMallPageByTitle_order));
	}
	
	final String save_sql = "insert into jvf_mall(gid,title,goodspic,costprice,price,inventory,currentnum,recommend,integral,type,state,html) values(?,?,?,?,?,?,?,?,?,?,?,?)";
	final String update_sql = "update jvf_mall set gid=?,title=?,goodspic=?,costprice=?,price=?,inventory=?,currentnum=?,recommend=?,integral=?,type=?,state=?,html=? where id=?";
	public void saveOrUpdate(final Mall mall) {
		if(mall.getId() == 0){
			this.execute(new ConnectionCallback<Object>(){
				public Object doInConnection(Connection conn)
						throws SQLException, DataAccessException {
					PreparedStatement ps = null;
					ResultSet rs = null;
					try{
						ps = conn.prepareStatement(save_sql, Statement.RETURN_GENERATED_KEYS);
						ps.setInt(1, mall.getGid());
						ps.setString(2, mall.getTitle());
						ps.setInt(3, mall.getGoodspic());
						ps.setFloat(4, mall.getCostprice());
						ps.setFloat(5, mall.getPrice());
						ps.setInt(6, mall.getInventory());
						ps.setInt(7, mall.getCurrentnum());
						ps.setInt(8, mall.getRecommend());
						ps.setInt(9, mall.getIntegral());
						ps.setInt(10, mall.getType());
						ps.setInt(11, mall.getState());
						ps.setString(12, mall.getHtml());
						ps.executeUpdate();
						rs = ps.getGeneratedKeys();
						if(rs.next())
							mall.setId(rs.getInt(1));
					}finally{
						JdbcUtil.close(rs, ps, null);
					}
					return null;
				}
				
			});
		}else
			this.update(update_sql,mall.getGid(),mall.getTitle(),mall.getGoodspic(),mall.getCostprice(),mall.getPrice(),mall.getInventory(),mall.getCurrentnum(),mall.getRecommend(),mall.getIntegral(),mall.getType(),mall.getState(),mall.getHtml(),mall.getId());
	}
	
	final String findMallById = "select id,gid,title,goodspic,costprice,price,inventory,currentnum,recommend,integral,type,state,html from jvf_mall where id=?";
	public Mall findMallById(final int id) {
		return (Mall) this.execute(findMallById, new PreparedStatementCallback<Mall>(){
			public Mall doInPreparedStatement(PreparedStatement ps)
					throws SQLException, DataAccessException {
				ResultSet rs = null;
				try{
					ps.setInt(1, id);
					rs = ps.executeQuery();
					if(rs.next()){
						Mall m = new Mall();
						m.setId(rs.getInt(1));
						m.setGid(rs.getInt(2));
						m.setTitle(rs.getString(3));
						m.setGoodspic(rs.getInt(4));
						m.setCostprice(rs.getFloat(5));
						m.setPrice(rs.getFloat(6));
						m.setInventory(rs.getInt(7));
						m.setCurrentnum(rs.getInt(8));
						m.setRecommend(rs.getInt(9));
						m.setIntegral(rs.getInt(10));
						m.setType(rs.getInt(11));
						m.setState(rs.getInt(12));
						m.setHtml(rs.getString(13));
						return m;
					}
					return null;
				}finally{
					JdbcUtil.closeResultSet(rs);
				}
			}
		});
	}
    
	//积分兑换
	public Object[] integralConversion(final Order order){
		return this.execute(new ConnectionCallback<Object[]>(){
			public Object[] doInConnection(Connection conn)
					throws SQLException, DataAccessException {
				ResultSet rs = null;
				Statement stmt = null;
				try{
					stmt = conn.createStatement();
					String sql = "select title,inventory,currentnum,integral from jvf_mall where id="+order.getAid()+" limit 1 for update";
					rs = stmt.executeQuery(sql);
					if(!rs.next()){
						return new Object[]{1,"所选商城商品信息有误"};
					}
					String m_title = rs.getString(1);
					int inventory = rs.getInt(2);
					int currentnum = rs.getInt(3);
					int integral = rs.getInt(4);
					order.setGoodsname(m_title);
					order.setOrderprice(integral*order.getNum());
					if(currentnum+order.getNum()>inventory){
						return new Object[]{2,"所选商城商品库存不足"};
					}
					JdbcUtil.closeResultSet(rs);
					sql = "select pointc from jvf_user where id="+order.getUid()+" limit 1 for update";
					rs = stmt.executeQuery(sql);
					if(!rs.next()){
						return new Object[]{3,"用户信息有误"};
					}
					if(rs.getInt(1) < order.getOrderprice()){
						return new Object[]{4,"用户积分余额不足"};
					}
					JdbcUtil.closeResultSet(rs);
					stmt.addBatch("update jvf_user set pointc=pointc-"+order.getOrderprice()+" where id="+order.getUid());
					stmt.addBatch("insert into jvf_pointclog(uid,name,payment,money,addtime,remark) values("
							+order.getUid()+",'"+order.getName()+"','商品兑换',"+order.getOrderprice()+",now(),'兑换商品,"+m_title+","+order.getNum()+"个,共消耗"+order.getOrderprice()+"积分')");
					if(currentnum+order.getNum() == inventory){
						stmt.addBatch("update jvf_mall set currentnum=currentnum+"+order.getNum()+" state=1 where id = "+order.getAid());
					}else{
						stmt.addBatch("update jvf_mall set currentnum=currentnum+"+order.getNum()+" where id = "+order.getAid());
					}
					stmt.executeBatch();
					saveOrder(conn,order);
					return new Object[]{0,"兑换成功"};
				}finally{
					JdbcUtil.closeStatement(stmt);
				}
			}
		});
	}
	
	//入订单表
	public int saveOrder(Connection connect,Order order) throws SQLException{
		String s = "insert into jvf_order(uid ,ordersn ,num ,aid ,goodsname ,orderprice ,name ,recipient ,address ,postalcode ,tel , remark ,type ,addtime ,state values(?,?,?,?,?,?,?,?,?,?,?,?,?,now(),?)";
		PreparedStatement psmt = null;
		try{
			psmt = connect.prepareStatement(s);
			psmt.setLong(1, order.getUid());
			psmt.setString(2, order.getOrdersn());
			psmt.setInt(3, order.getNum());
			psmt.setInt(4, order.getAid());
			psmt.setString(5, order.getGoodsname());
			psmt.setDouble(6, order.getOrderprice());
			psmt.setString(7, order.getName());
			psmt.setString(8, order.getRecipient());
			psmt.setString(9, order.getAddress());
			psmt.setString(10, order.getPostalcode());
			psmt.setString(11, order.getTel());
			psmt.setString(12, order.getRemark());
			psmt.setInt(13, order.getType());
			psmt.setInt(14, order.getState());
			int c = psmt.executeUpdate();
			return c;
		}finally{
			JdbcUtil.closeStatement(psmt);
		}
	}
	
	//入积分兑换日志表
	public int savePointcLog(Connection connect,PointcLog plog) throws SQLException{
		String s = "insert into jvf_pointclog(uid,name,payment,money,addtime,remark) values(?,?,?,?,now(),?)";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setLong(1, plog.getUid());
		psmt.setString(2, plog.getName());
		psmt.setString(3, plog.getPayment());
		psmt.setInt(4, plog.getMoney());
		psmt.setString(5, plog.getRemark());
		int c = psmt.executeUpdate();
		JdbcUtil.close(null, psmt, null);
		return c;
	}
	
	//扣用户积分
	public int updateUserPointc(Connection connect,int pointc,long userid) throws SQLException{
		String s = "update jvf_user set pointc = pointc - ? where id = ?";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setInt(1, pointc);
		psmt.setLong(2, userid);
		int c = psmt.executeUpdate();
		JdbcUtil.close(null, psmt, null);
		return c;
	}
	
	//更新状态
	public int updateMallState(Connection connect,int state,long id) throws SQLException{
		String s = "update jvf_mall set state = ? where id = ?";
		PreparedStatement psmt = connect.prepareStatement(s);
		psmt.setInt(1, state);
		psmt.setLong(2, id);
		int c = psmt.executeUpdate();
		JdbcUtil.close(null, psmt, null);
		return c;
	}
	
	final String findMallPage_count = "select count(1) from jvf_mall m left join jvf_goods g on g.id=m.gid left join jvf_accessory a on a.id=g.goodspic";
	final String findMallPage_list = "select m.id,m.gid,m.title,m.costprice,m.price,m.inventory,m.currentnum,m.recommend,m.integral,m.type,m.state,m.html,a.thumbnail from jvf_mall m left join jvf_goods g on g.id=m.gid left join jvf_accessory a on a.id=m.goodspic";
	final String findMallPage_order = " order by m.id";
	public Page findMallPage(int pageno, int pagesize, int cid, int startScore,
			int endScore,int state) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if(cid > 0){
			query.append(" and g.cid=").append(cid);
		}
		if(startScore > 0){
			query.append(" and m.integral>=").append(startScore);
		}
		if(endScore > 0){
			query.append(" and m.integral<=").append(endScore);
		}
		query.append(" and m.state=").append(state);
		return findPage(pageno,pagesize,new StringBuilder(findMallPage_count).append(query).toString(),new StringBuilder(findMallPage_list).append(query).append(findMallPage_order));
	}

	final String updateState = "update jvf_mall set state=? where id=?";
	public void updateState(int id, int state) {
		this.update(updateState,state,id);
	}
}
